﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ProdeManagmente.Libs;
using ProdeManagmente.Model;
using System.Data;

namespace ProdeManagmente.DAO
{
    public class UserResultService
    {

        private static UserResultService instance;
        private static String TBUserResult = "UserResult";

        private UserResultService()
        { }

        public static UserResultService getUserResultServiceInstance()
        {
            if (instance == null)
            {
                instance = new UserResultService();
            }
            return instance;
        }

        public UserResult getUserResultByidUserAndIdMatch(long idUser, long idMatch)
        {
            UserResult userResult = null;

            Persistencia conn = Persistencia.getInstance();

            String SQL = "SELECT * FROM " + TBUserResult + " " +
                            "WHERE idMatch = " + idMatch + " AND " +
                            "idUser = " + idUser + " ";

            DataTable userResultTable = conn.executeQuery(SQL);

            foreach (DataRow userResultRow in userResultTable.Rows)
            {
                userResult = getUserResult(userResultRow, new List<User>(), new List<Match>());
            }

            return userResult;
        }

        public List<UserResult> getUserResultByIdMatch(long idMatch)
        {
            List<UserResult> userResultList = new List<UserResult>();

            Persistencia conn = Persistencia.getInstance();

            String SQL = "SELECT * FROM " + TBUserResult + " " +
                            "WHERE idMatch = " + idMatch;

            DataTable userResultTable = conn.executeQuery(SQL);

            foreach (DataRow userResultRow in userResultTable.Rows)
            {
                UserResult userResult = getUserResult(userResultRow, new List<User>(), new List<Match>());
                userResultList.Add(userResult);
            }
            return userResultList;
        }

        public List<UserResult> getUserResultByIdUser(long idUser)
        {
            List<UserResult> userResultList = new List<UserResult>();

            Persistencia conn = Persistencia.getInstance();

            String SQL = "SELECT * FROM " + TBUserResult + " " +
                            "WHERE idUser = " + idUser;

            DataTable userResultTable = conn.executeQuery(SQL);

            foreach (DataRow userResultRow in userResultTable.Rows)
            {
                UserResult userResult = getUserResult(userResultRow, new List<User>(), new List<Match>());
                userResultList.Add(userResult);
            }
            return userResultList;
        }

        /**
         * Obtiene la lista de Resultados,  filtro SIN la palabra clave WHERE
         */
        public List<UserResult> getUserResultListFilter(String filter)
        {
            UserService userService = UserService.getUserServiceInstance();
            MatchService matchService = MatchService.getMatchServiceInstance();
            List<UserResult> userResultList = new List<UserResult>();

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBUserResult + " ";

            if (!filter.Trim().Equals(""))
            {
                SQL = SQL + "WHERE " + filter;
            }

            DataTable userResultTable = conn.executeQuery(SQL);

            List<User> users = new List<User>();
            users.AddRange(userService.getUserListFilter(""));

            List<Match> matches = new List<Match>();
            matches.AddRange(matchService.getMatchListFilter(""));

            foreach (DataRow userResultRow in userResultTable.Rows)
            {
                UserResult userResult = getUserResult(userResultRow, users, matches);
                userResultList.Add(userResult);
            }
            return userResultList;
        }

        public UserResult save(UserResult userResult)
        {
            Persistencia conn = Persistencia.getInstance();
            String SQL = "";

            if (getUserResultByidUserAndIdMatch(userResult.getUser().getId(), userResult.getMatch().getId()) != null) //Si es diferente de null es por que ya existe entonces tiene que actualizarlo
            {
                SQL = "UPDATE  " + TBUserResult + " SET " +
                      "VTeam1 = " + userResult.getVTeam1() + ", " +
                      "VTeam2 = " + userResult.getVTeam2() + ", " +
                      "Points = " + userResult.getPoints() + " " +
                      "WHERE idUser = " + userResult.getUser().getId() + " AND " +
                      "idMatch = " + userResult.getMatch().getId() + " ";
            }
            else //Si es null es por que es un objeto nuevo
            {
                SQL = "INSERT INTO " + TBUserResult + " " +
                       "(idUser, idMatch, VTeam1, VTeam2, Points) VALUES (" +
                       "" + userResult.getUser().getId() + ", " +
                       "" + userResult.getMatch().getId() + ", " +
                       "" + userResult.getVTeam1() + ", " +
                       "" + userResult.getVTeam2() + ", " +
                       "" + userResult.getPoints() + ") ";
            }

            int resultNumber = Convert.ToInt32(conn.ExecuteNonQuery(SQL));
            if (resultNumber <= 0)
            {
                throw new BusinessException("302", "No se pudo grabar el elemento");
            }

            return userResult;
        }

        public void delete(long idUser, long idMatch)
        {
            Persistencia conn = Persistencia.getInstance();
            String SQL = "";

            SQL = "DELETE  " + TBUserResult + "  " +
                    "WHERE idUser = " + idUser + " AND " +
                    "idMatch = " + idMatch + " ";

            int result = Convert.ToInt32(conn.ExecuteNonQuery(SQL));
            if (result <= 0)
            {
                throw new BusinessException("303", "No se pudo borrar el elemento");
            }
        }
        
        private UserResult getUserResult(DataRow userResultRow, List<User> users, List<Match> matches)
        {
            UserResult userResult = null;

            MatchService matchService = MatchService.getMatchServiceInstance();
            UserService userService = UserService.getUserServiceInstance();

            long idMatch = Convert.ToInt64(userResultRow["idMatch"].ToString());
            long idUser = Convert.ToInt64(userResultRow["idUser"].ToString());

            Match match = null;
            User user = null;

            match = matches.Find(x => x.getId() == idMatch);
            user = users.Find(x => x.getId() == idUser);

            if (match == null)
            {
                match = matchService.getMatchById(idMatch);
            }

            if (user == null)
            {
                user = userService.getUserById(idUser);
            }

            userResult = new UserResult(user,
                            match,
                            Convert.ToInt32(userResultRow["VTeam1"].ToString()),
                            Convert.ToInt32(userResultRow["VTeam2"].ToString()),
                            Convert.ToInt32(userResultRow["Points"].ToString()));

            return userResult;
        }
    }
}
